package com.gugee.tiktok.web.repository;


import com.gugee.tiktok.web.dto.AwemeCountDto;
import com.gugee.tiktok.web.dto.AwemeResponse;
import com.gugee.tiktok.web.model.mysql.Aweme;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface AwemeRepository extends CrudRepository<Aweme,Long>, AwemeExtRepository {

    @Query("SELECT id,o_aweme_id aweme_id,o_cover cover,o_dynamic_cover dynamic_cover,o_share_url share_url,o_desc title,o_create_time create_time,score,o_play_count views,o_digg_count likes, o_comment_count reviews,o_share_count shares  FROM aweme WHERE o_uid=:uid and o_create_time BETWEEN :startTime AND :endTime ORDER BY o_create_time DESC LIMIT 0,10")
    List<AwemeResponse> findTopAwemes(long uid, long startTime, long endTime);

    @Query("SELECT * FROM aweme WHERE O_uid = :uid ORDER BY o_create_time DESC limit 0,:size")
    List<Aweme> findAwemeByUidOrderByCreateTime(long uid, int size);

    @Query("SELECT count(*) FROM aweme WHERE O_uid = :uid")
    int countAwemeByUid(long uid);


    @Query("SELECT COUNT(*) FROM aweme WHERE o_uid=:uid AND o_create_time BETWEEN  :startTime AND :endTime")
    Integer countAwemeByOCreateTime(long uid,long startTime,long endTime);

    @Query("SELECT COUNT(*) FROM aweme WHERE o_uid=:uid AND o_create_time BETWEEN :startTime AND :endTime")
    Integer countByOUidAndOCreateTimeBetween(long uid,long startTime,long endTime);


    Optional<Aweme> findByoAwemeId(long awemeId);

    @Query("select " +
            "DAYOFWEEK(FROM_UNIXTIME(o_create_time,'%Y-%m-%d %H:%i:%s')) time" +
            ",count(*) count " +
            "from aweme " +
            "where o_uid = :uid  and " +
            "o_create_time > :startTime " +
            "group by time order by time")
    List<AwemeCountDto> countDayByUid(long uid, long startTime);

    @Query("select " +
            "FROM_UNIXTIME(o_create_time,'%H') time,count(*) count " +
            "from aweme " +
            "where o_uid = :uid and o_create_time > :startTime group by time order by time")
    List<AwemeCountDto> countHourByUid(long uid, long startTime);
}
